﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using ProjectWizardManagementv1.WpfComponents.ComponentClasses;

namespace ProjectWizardManagementv1.Infrastructure.Concrete
{
    public class ExcelDocumentIO
    {
        private static readonly Regex _gridPattern = new Regex("([A-Z]+)([0-9]+)");
        private const int COLUMN_GROUP = 1;
        private const int ROW_GROUP = 2;

        public static void SaveDocument(CellTextField[,] grid, string destinationPath)
        {
            using(SpreadsheetDocument document = SpreadsheetDocument.Open(destinationPath, true))
            {
                var theSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();

                var shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any() ?
                    document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First() : document.WorkbookPart.AddNewPart<SharedStringTablePart>();

                var sheet = ((WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id))).Worksheet;
                var rows = sheet.Descendants<Row>().ToList();

                var editedCells = grid.Cast<CellTextField>().Where(c => c.HasBeenEdited);
                foreach (var cell in editedCells)
                        UpdateCell(cell, rows, sheet, shareStringPart);

                sheet.Save();
            }
        }

        private static void UpdateCell(CellTextField gridCell, List<Row> rows, Worksheet sheet, SharedStringTablePart sharedStringTable)
        {
            int index = GetIndexFromSharedStringTable(gridCell.StringText, sharedStringTable);
            Cell cell = null;
            bool isExistingCell = rows.Any(r => r.Descendants<Cell>().Select(c => c.CellReference == gridCell.Reference).Any());
            if(isExistingCell && !string.IsNullOrEmpty(gridCell.OriginalText))
            {
                foreach(var row in rows)
                {
                    if(row.Elements<Cell>().Any(c => c.CellReference == gridCell.Reference))
                    {
                        cell = row.Elements<Cell>().First(c => c.CellReference == gridCell.Reference);
                        break;
                    }
                }

            }
            else
            {
                var columnDimension = _gridPattern.Match(gridCell.Reference).Groups[COLUMN_GROUP].Value;
                var rowDimension = _gridPattern.Match(gridCell.Reference).Groups[ROW_GROUP].Value;

                Row row;
                if(rows.Count(r => r.RowIndex == uint.Parse(rowDimension)) != 0)
                {
                    row = rows.First(r => r.RowIndex == uint.Parse(rowDimension));
                }
                else
                {
                    row = new Row{RowIndex = uint.Parse(rowDimension)};
                    sheet.Append(row);
                }

                if(row.Elements<Cell>().Any(c => c.CellReference == columnDimension + rowDimension))
                    cell = row.Elements<Cell>().First(c => c.CellReference.Value == gridCell.Reference);
                else
                {
                    cell = FindCell(row, gridCell.Reference);
                    sheet.Save();
                }
            }
            cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture));
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            gridCell.OriginalText = gridCell.StringText;

        }

        private static int GetIndexFromSharedStringTable(string text, SharedStringTablePart sharedStringTable)
        {
            if(sharedStringTable.SharedStringTable == null)
                sharedStringTable.SharedStringTable = new SharedStringTable();

            int i = 0;
            foreach(SharedStringItem item in sharedStringTable.SharedStringTable.Elements<SharedStringItem>())
            {
                if(item.InnerText == text)
                {
                    return i;
                }
                i++;
            }

            sharedStringTable.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
            sharedStringTable.SharedStringTable.Save();

            return i;
        }

        private static Cell FindCell(Row row, string reference)
        {
            Cell refCell = row.Elements<Cell>().FirstOrDefault(cell => String.Compare(cell.CellReference.Value, reference, StringComparison.OrdinalIgnoreCase) > 0);

            var newCell = new Cell() {CellReference = reference};
            row.InsertBefore(newCell, refCell);
            return newCell;
        }

    }
}
